*****************************************************************
* Replication directory for                                   ***
* Prime locations                                             ***
* by Gabriel M. Ahlfeldt, Thilo N.H. Albers, Kristian Behrens ***
* Published in American Economic Review: Insights             ***
*****************************************************************
* 01/2025
* Stata
version 17.0

* This do file tabulates NETS establishment data to produce the outputs reported in Table A.1.2
* It uses files generated by the C++ code in 'extract_NETS' 
* These files contain proprietary and not part of the replication directory. 
* To access the proprietary establishment-level data, please contact dwalls2@earthlink.net.  


local cbsa_list "10180 10380 10420 10500 10540 10580 10740 10780 10900 11020 11100 11180 11260 11460 11500 11540 11640 11700 12020 12060 12100 12220 12260 12420 12540 12580 12620 12700 12940 12980 13020 13140 13220 13380 13460 13740 13780 13820 13900 13980 14010 14020 14100 14260 14460 14500 14540 14740 14860 15180 15260 15380 15500 15540 15680 15940 15980 16020 16060 16180 16220 16300 16540 16580 16620 16700 16740 16820 16860 16940 16980 17020 17140 17300 17420 17460 17660 17780 17820 17860 17900 17980 18020 18140 18580 18700 18880 19060 19100 19140 19180 19300 19340 19380 19460 19500 19660 19740 19780 19820 20020 20100 20220 20260 20500 20700 20740 20940 21060 21140 21300 21340 21500 21660 21780 21820 22020 22140 22180 22220 22380 22420 22500 22520 22540 22660 22900 23060 23420 23460 23540 23580 23900 24020 24140 24220 24260 24300 24340 24420 24500 24540 24580 24660 24780 24860 25020 25060 25180 25220 25260 25420 25500 25540 25620 25860 25940 25980 26140 26300 26380 26420 26580 26620 26820 26900 26980 27060 27100 27140 27180 27260 27340 27500 27620 27740 27780 27860 27900 27980 28020 28100 28140 28420 28660 28700 28740 28940 29020 29100 29180 29200 29340 29420 29460 29540 29620 29700 29740 29820 29940 30020 30140 30300 30340 30460 30620 30700 30780 30860 30980 31020 31080 31140 31180 31340 31420 31460 31540 31700 31740 31860 31900 32420 32580 32780 32820 32900 33100 33140 33220 33260 33340 33460 33540 33660 33700 33740 33780 33860 34060 34100 34580 34620 34740 34820 34900 34940 34980 35100 35300 35380 35620 35660 35840 35980 36100 36140 36220 36260 36420 36500 36540 36740 36780 36980 37100 37340 37460 37620 37860 37900 37980 38060 38220 38300 38340 38540 38660 38860 38900 38940 39140 39300 39340 39380 39460 39540 39580 39660 39740 39820 39900 40060 40140 40220 40340 40380 40420 40580 40660 40900 40980 41060 41100 41140 41180 41420 41500 41540 41620 41660 41700 41740 41860 41900 41940 41980 42020 42100 42140 42200 42220 42340 42540 42660 42680 42700 43100 43300 43340 43420 43580 43620 43780 43900 44060 44100 44140 44180 44220 44300 44420 44700 44940 45060 45220 45300 45460 45500 45540 45780 45820 45940 46060 46140 46220 46340 46520 46540 46660 46700 47020 47220 47260 47300 47380 47460 47580 47900 47940 48060 48140 48260 48300 48540 48620 48660 48700 48900 49020 49180 49340 49420 49620 49660 49700 49740"


foreach cbsa in `cbsa_list' {
	
	qui import delimited "clustering_cmd/data/cbsa/plants/plants_`cbsa'.txt", encoding(UTF-8) clear
	
	qui ren v3 NAICS6
	qui gen NAICS2 = floor(NAICS6/10000)

	qui gen type = "other"

	qui replace type = "mfg" if (NAICS2 >= 31 & NAICS2 <= 33) | NAICS2 == 42 | (NAICS2 >= 48 & NAICS2 <= 49)
 
	qui replace type = "nts" if (NAICS2 >= 44 & NAICS2 <= 45) | (NAICS2 >= 71 & NAICS2 <= 72)

	qui replace type = "pbs" if (NAICS2 >= 61 & NAICS2 <= 62) | NAICS2 == 92

	qui replace type = "ts" if (NAICS2 >= 51 & NAICS2 <= 55)
	
	qui count 
	local totalc = r(N)
	
	qui count if type == "mfg"
	local emp0 = r(N)
	
	qui count if type == "nts"
	local emp1 = r(N)
	
	qui count if type == "pbs"
	local emp2 = r(N)
	
	qui count if type == "ts"
	local emp3 = r(N)
	
	qui count if type == "other"
	local emp4 = r(N)
	
	clear
	
	set obs 1
	gen cbsa = `cbsa'
	gen totalc = `totalc'
	gen type0 = `emp0'
	gen type1 = `emp1'
	gen type2 = `emp2'
	gen type3 = `emp3'
	gen type4 = `emp4'
	
	sort cbsa totalc type0 type1 type2 type3 type4
	
	save "temp/temp`cbsa'.dta", replace
}

use "temp/temp10180.dta", clear

foreach cbsa in `cbsa_list' {
	
	append using "temp/temp`cbsa'.dta"
}

duplicates drop cbsa, force


save "temp/all_cbsa.dta", replace

gen others = 0
replace others = 1 if cbsa == 16980
replace others = 2 if cbsa == 31080
replace others = 3 if cbsa == 35620

egen total_type0 = sum(type0), by(others)
egen total_type1 = sum(type1), by(others)
egen total_type2 = sum(type2), by(others)
egen total_type3 = sum(type3), by(others)
egen total_type4 = sum(type4), by(others)
egen total_count = sum(totalc), by(others)
duplicates drop others, force

drop type0 type1 type2 type3 type4 totalc
replace cbsa = . if others == 0

* Script ends